# coding: utf-8
from importlib.metadata import requires
import xlwt
import xlrd
import requests
import sqlalchemy
from flask import Flask, request, jsonify, make_response
from flask_apscheduler import APScheduler
from flask_sqlalchemy import SQLAlchemy
from flask_profiler import Profiler
from flask_cors import CORS
from sqlalchemy import func
from io import BytesIO
from urllib import parse
from datetime import datetime, date, timedelta

app = Flask(__name__)
app.config["DEBUG"] = False
app.config["JSON_AS_ASCII"] = False

SQLALCHEMY_BINDS = {
    "ruoyi-vue": "mysql+pymysql://sorel:crea1002=@127.0.0.1:3306/ry-vue?charset=utf8",
    "sqlserver": f"mssql+pymssql://sa:{parse.quote_plus('yckfq123!@#')}@172.18.10.3:1433/KFQSL?charset=GBK",
    "rtu": "mysql+pymysql://sorel:crea1002=@127.0.0.1:3306/rtudata?charset=utf8",
}
app.config["SQLALCHEMY_BINDS"] = SQLALCHEMY_BINDS
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["flask_profiler"] = {
    "enabled": app.config["DEBUG"],
    "storage": {"engine": "sqlite"},
    "basicAuth": {"enabled": True, "username": "admin", "password": "admin"},
    "ignore": ["^/static/.*"],
}

db = SQLAlchemy(app)
scheduler = APScheduler()
CORS(app, resources=r"/*")


class Location(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_location"
    id = db.Column(db.String(20), nullable=True, primary_key=True)
    name = db.Column(db.String(50), nullable=True)
    station_id = db.Column(db.String(50), nullable=True)
    lng = db.Column(db.String(30), nullable=True)
    lat = db.Column(db.String(30), nullable=True)
    type = db.Column(db.String(12), nullable=True)
    addr = db.Column(db.String(30), nullable=True)
    status = db.Column(db.String(12), nullable=True)

    def data(self):
        return dict(
            [
                (key, self.__dict__[key])
                for key in ["id", "name", "station_id", "lat", "lng", "addr", "type", "status"]
            ]
        )


class Device(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_device"
    id = db.Column(db.Integer, nullable=True, primary_key=True, autoincrement=True)
    eq_id = db.Column(db.String(20), nullable=True)
    eq_name = db.Column(db.String(50), unique=True)
    station_id = db.Column(db.String(20), nullable=True)
    station_name = db.Column(db.String(50), nullable=True)
    type = db.Column(db.String(20), nullable=True)
    status = db.Column(db.String(10), nullable=True)
    lat = db.Column(db.String(30))
    lng = db.Column(db.String(30))
    addr = db.Column(db.String(255))
    info = db.Column(db.String(255))

    def __init__(
        self,
        eq_id,
        eq_name,
        station_id,
        station_name,
        device_type,
        status,
        lat,
        lng,
        addr,
        info,
        device_id=None,
    ) -> None:
        super().__init__()
        self.id = device_id
        self.eq_id = eq_id
        self.eq_name = eq_name
        self.station_id = station_id
        self.station_name = station_name
        self.type = device_type
        self.status = status
        self.lat = lat if lat != "undefined" else ""
        self.lng = lng if lng != "undefined" else ""
        self.addr = addr if addr != "undefined" else ""
        self.info = info if info != "undefined" else ""

    def data(self):
        return dict(
            [
                (key, self.__dict__[key])
                for key in [
                    "id",
                    "eq_id",
                    "eq_name",
                    "station_id",
                    "station_name",
                    "type",
                    "status",
                    "lat",
                    "lng",
                    "addr",
                    "info",
                ]
            ]
        )


class Equipment(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_equipment"
    eq_id = db.Column(db.String(20), nullable=True, primary_key=True)
    eq_name = db.Column(sqlalchemy.types.NVARCHAR(100), unique=True)
    eq_type_id = db.Column(db.String(30), nullable=True)
    station_id = db.Column(db.String(30), nullable=True)
    isenable = db.Column(db.String(12), nullable=True)
    status = db.Column(db.String(12), nullable=True)

    def data(self):
        return dict(
            [(key, self.__dict__[key]) for key in ["station_id", "eq_id", "eq_name", "eq_type_id"]]
        )


class Channel(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_channel"
    channel = db.Column(db.String(20), nullable=True, primary_key=True)
    location = db.Column(db.String(50), nullable=True)
    name = db.Column(db.String(50), nullable=True)
    device = db.Column(db.String(30), nullable=True)
    status = db.Column(db.String(30), nullable=True)

    def data(self):
        return dict(
            [
                (key, self.__dict__[key])
                for key in ["location", "channel", "name", "device", "status"]
            ]
        )


class Token(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_token"
    id = db.Column(db.String(20), nullable=True, primary_key=True)
    name = db.Column(db.String(50), nullable=True)
    token = db.Column(db.String(255), nullable=True)

    def data(self):
        return dict([(key, self.__dict__[key]) for key in ["id", "name", "token"]])


class DutyInfo(db.Model):
    __bind_key__ = "ruoyi-vue"
    __tablename__ = "dev_duty"
    id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
    title = db.Column(db.String(50), nullable=True)
    duty_date = db.Column(db.Date, nullable=True)
    leader_charge = db.Column(db.String(50))
    leader_duty = db.Column(db.String(50))
    personnel_day = db.Column(db.String(50))
    personnel_night = db.Column(db.String(50))
    duty_phone = db.Column(db.String(50))
    dept = db.Column(db.String(50))
    remark = db.Column(db.String(255))

    def __init__(
        self,
        title,
        duty_date,
        leader_charge,
        leader_duty,
        personnel_day,
        personnel_night,
        duty_phone,
        dept,
        remark,
        duty_id=None,
    ) -> None:
        super().__init__()
        self.id = duty_id
        self.title = title
        self.duty_date = duty_date
        self.leader_charge = leader_charge
        self.leader_duty = leader_duty
        self.personnel_day = personnel_day
        self.personnel_night = personnel_night
        self.duty_phone = duty_phone
        self.dept = dept
        self.remark = remark if remark != "undefined" else ""

    def data(self, timeFormat=None):
        res = dict(
            [
                (key, self.__dict__[key])
                for key in [
                    "id",
                    "title",
                    "duty_date",
                    "leader_charge",
                    "leader_duty",
                    "personnel_day",
                    "personnel_night",
                    "duty_phone",
                    "dept",
                    "remark",
                ]
            ]
        )
        if timeFormat:
            res["duty_date"] = res["duty_date"].strftime(timeFormat)
        return res


class Station(db.Model):
    __bind_key__ = "sqlserver"
    __tablename__ = "T_Station"
    station_id = db.Column(db.String(20), nullable=True, primary_key=True)
    station_name = db.Column(db.String(50), nullable=True)
    longitude = db.Column(db.String(30), nullable=True)
    latitude = db.Column(db.String(30), nullable=True)
    isenable = db.Column(db.String(12), nullable=True)
    station_type = db.Column(db.String(30), nullable=True)
    stcd = db.Column(db.String(12), nullable=True)
    sttp = db.Column(db.String(12), nullable=True)
    OrderNum = db.Column(db.String(255), nullable=False)

    def data(self):
        return dict(
            [
                (key, self.__dict__[key])
                for key in ["station_id", "station_name", "longitude", "latitude"]
            ]
        )


class WaterLevel(db.Model):
    __bind_key__ = "sqlserver"
    __tablename__ = "T_Record_WaterLevel"
    id = db.Column(db.Integer, nullable=True, primary_key=True)
    eq_id = db.Column(db.String(20), nullable=True)
    collect_time = db.Column(db.DateTime)
    height = db.Column(db.String(30), nullable=True)

    def data(self, timeFormat=None):
        res = dict([(key, self.__dict__[key]) for key in ["eq_id", "collect_time", "height"]])
        if timeFormat:
            res["collect_time"] = res["collect_time"].strftime(timeFormat)
        return res


class WaterRTU(db.Model):
    __bind_key__ = "rtu"
    __tablename__ = "st_rsvr_r"
    stcd = db.Column(db.Integer, nullable=True, primary_key=True)
    tm = db.Column(db.DateTime)
    rz = db.Column(db.String(30), nullable=True)
    blrz = db.Column(db.String(30), nullable=True)

    def data(self, timeFormat=None):
        res = dict([(key, self.__dict__[key]) for key in ["stcd", "tm", "rz", "blrz"]])
        if timeFormat:
            res["tm"] = res["tm"].strftime(timeFormat)
        return res


class RainFall(db.Model):
    __bind_key__ = "sqlserver"
    __tablename__ = "T_Record_RainFall_Hour"
    id = db.Column(db.Integer, nullable=True, primary_key=True)
    eq_id = db.Column(db.String(20), nullable=True)
    collect_time = db.Column(db.DateTime)
    value = db.Column(db.String(30), nullable=True)

    def data(self, timeFormat=None):
        res = dict([(key, self.__dict__[key]) for key in ["eq_id", "collect_time", "value"]])
        if timeFormat:
            res["collect_time"] = res["collect_time"].strftime(timeFormat)
        return res


@app.route("/location", methods=["GET"])
def getLocation():
    items = Location.query.filter(Location.type == request.args.get("type")).all()
    items = [item.data() for item in items]
    return jsonify(items)


# deprecated
@app.route("/location/equipment", methods=["GET"])
def getLocationAndEquipment():
    query_type = request.args.get("type")
    locations = Location.query.filter(Location.type == query_type).all()
    locations = [item.data() for item in locations]
    now = datetime.now()
    _8AM = datetime.strptime(str(datetime.now().date()) + "08:00", "%Y-%m-%d%H:%M")
    if now > _8AM:
        start = _8AM
    else:
        start = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d") + "08:00"
        start = datetime.strptime(start, "%Y-%m-%d%H:%M")
    for location in locations:
        items = Equipment.query.filter(Equipment.station_id == location["station_id"]).all()
        items = [item.data() for item in items]
        equipments = []
        for item in items:
            if query_type == "water" and (
                item["eq_name"] in ["外河", "内河", "外河水位", "内河水位"]
            ):
                waterlevel = (
                    WaterLevel.query.filter(WaterLevel.eq_id == item["eq_id"])
                    .order_by(WaterLevel.id.desc())
                    .limit(1)
                    .all()
                )
                waterlevel = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in waterlevel]
                item["waterlevel"] = waterlevel
                equipments.append(item)
            if query_type == "rain" and (item["eq_name"] in ["雨量"]):
                # rainfall = RainFall.query.filter(RainFall.eq_id == item['eq_id']).order_by(RainFall.id.desc()).limit(1).all()
                rainfall = RainFall.query.filter(
                    RainFall.eq_id == item["eq_id"], RainFall.collect_time > start
                ).all()
                rainfall = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in rainfall]
                item["rainfall"] = sum([item["value"] for item in rainfall])
                item["updateTime"] = rainfall[-1]["collect_time"]
                equipments.append(item)
        location["equipments"] = equipments
    return jsonify(locations)


low = 0.30
high = 1.80


@app.route("/station/list/water", methods=["GET"])
def listStationWater():
    device_list = Device.query.filter(Device.type == "water", Device.status == 1).all()
    device_list = [item.data() for item in device_list]
    eq_ids = [item["eq_id"] for item in device_list]
    latest_ids = (
        db.session.query(WaterLevel.eq_id, func.max(WaterLevel.id)).group_by(WaterLevel.eq_id).all()
    )
    latest_ids = [item[1] for item in latest_ids if item[0] in eq_ids]
    items = WaterLevel.query.filter(WaterLevel.id.in_(latest_ids)).all()
    items = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in items]
    for device in device_list:
        device["collect_time"] = ""
        device["height"] = ""
    for device in device_list:
        for item in items:
            if device["eq_id"] == item["eq_id"]:
                device["collect_time"] = item["collect_time"]
                device["height"] = item["height"]
    station_list, station_names = [], []
    for device in device_list:
        if device["station_name"] not in station_names:
            station_names.append(device["station_name"])
            station_info = {
                "staion_id": device["station_id"],
                "station_name": device["station_name"],
                "lat": device["lat"],
                "lng": device["lng"],
            }
            station_list.append(station_info)
    for device in device_list:
        for station in station_list:
            if device["station_name"] == station["station_name"]:
                if "内河" in device["eq_name"]:
                    try:
                        fix = float(device["info"])
                    except:
                        fix = 0
                    height = float(device["height"]) + fix if device["height"] else ""
                    if type(height) == float:
                        height = round(height, 2)
                        if height < low:
                            height = round(low, 2)
                        if height > high:
                            height = round(high, 2)
                        height = f"{height:.2f}"
                    station["inner"] = {
                        "eq_id": device["eq_id"],
                        "eq_name": device["eq_name"],
                        "collect_time": device["collect_time"],
                        "height": height,
                    }
                if "外河" in device["eq_name"]:
                    try:
                        fix = float(device["info"])
                    except:
                        fix = 0
                    height = float(device["height"]) + fix if device["height"] else ""
                    if type(height) == float:
                        height = round(height, 2)
                        if height < low:
                            height = round(low, 2)
                        if height > high:
                            height = round(high, 2)
                        height = f"{height:.2f}"
                    station["outer"] = {
                        "eq_id": device["eq_id"],
                        "eq_name": device["eq_name"],
                        "collect_time": device["collect_time"],
                        "height": height,
                    }
    # subquery = db.session.query(WaterRTU.stcd, func.last(WaterRTU.tm).label("latest")).group_by(WaterRTU.stcd).subquery()
    # query = db.session.query(WaterRTU).join(subquery, WaterRTU.stcd == subquery.c.stcd and WaterRTU.tm == subquery.c.latest)
    engine = sqlalchemy.create_engine(SQLALCHEMY_BINDS["rtu"])
    query = engine.execute(
        """select t.stcd, t.tm, t.rz, t.blrz
                                    from st_rsvr_r t
                                    inner join(
                                        select stcd, max(tm) as latest 
                                        from st_rsvr_r 
                                        group by stcd
                                    ) t1 on t.stcd = t1.stcd and t.tm = t1.latest;"""
    )
    water_rtu_data = query.fetchall()
    mapping = {
        "00000001": "团结河闸站",
        "00000002": "新生河闸站",
        "00000003": "新界河闸站",
        "00000004": "潮中河闸站",
        "00000005": "南洋中心河闸站",
        "00000006": "伍龙河东闸站",
    }
    data = []
    timeFormat = "%Y-%m-%d %H:%M"
    for item in water_rtu_data:
        data_item = {
            "station_name": mapping.get(item.stcd),
            "inner": {
                "eq_id": "内河水位",
                "height": item.rz if item.rz is not None else "",
                "collect_time": item.tm.strftime(timeFormat),
            },
            "outer": {
                "eq_id": "外河水位",
                "height": item.blrz if item.blrz is not None else "",
                "collect_time": item.tm.strftime(timeFormat),
            },
        }
        if data_item["station_name"] == "伍龙河东闸站":
            data_item["outer"]["height"] = data_item["inner"]["height"]
        data.append(data_item)
    for i, station in enumerate(station_list):
        if station["station_name"] in mapping.values():
            for item in data:
                if station["station_name"] == item["station_name"]:
                    station_list[i] = item
    return jsonify(station_list)


@app.route("/station/list/rain", methods=["GET"])
def listStationRain():
    device_list = Device.query.filter(Device.type == "rain", Device.status == 1).all()
    device_list = [item.data() for item in device_list]
    now = datetime.now()
    _8AM = datetime.strptime(str(datetime.now().date()) + "08:00", "%Y-%m-%d%H:%M")
    if now > _8AM:
        start = _8AM
    else:
        start = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d") + "08:00"
        start = datetime.strptime(start, "%Y-%m-%d%H:%M")
    for device in device_list:
        items = RainFall.query.filter(
            RainFall.eq_id == device["eq_id"], RainFall.collect_time >= start
        ).all()
        items = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in items]
        if items:
            dayRain = sum([item["value"] for item in items])
            device["day_rain"] = dayRain
            device["update_time"] = items[-1]["collect_time"]
        else:
            device["day_rain"] = 0
            device["update_time"] = _8AM.strftime("%Y-%m-%d %H:%M")
    return jsonify(device_list)


@app.route("/station/list/cam", methods=["GET"])
def listStationCam():
    device_list = Device.query.filter(Device.type == "cam", Device.status == 1).all()
    device_list = [item.data() for item in device_list]
    station_list, station_names = [], []
    for device in device_list:
        if device["station_name"] not in station_names:
            station_names.append(device["station_name"])
            station_info = {
                "staion_id": device["station_id"],
                "station_name": device["station_name"],
                "lat": device["lat"],
                "lng": device["lng"],
                "addr": device["addr"],
                "cam": [],
            }
            station_list.append(station_info)
    for device in device_list:
        for station in station_list:
            if device["station_name"] == station["station_name"]:
                cam = {
                    "name": device["eq_name"],
                    "channel": device["info"],
                    "status": device["status"],
                }
                station["cam"].append(cam)
    return jsonify(station_list)


@app.route("/channel", methods=["GET"])
def getChannel():
    items = Channel.query.filter(Channel.location == request.args.get("location")).all()
    items = [item.data() for item in items]
    return jsonify(items)


@app.route("/token", methods=["GET"])
def getToken():
    items = Token.query.filter(Token.name == request.args.get("name")).all()
    items = [item.data() for item in items]
    return jsonify(items)


@app.route("/duty/list", methods=["GET"])
def listDutyInfo():
    start, end = request.args.get("start"), request.args.get("end")
    start = datetime.strptime(start, "%Y-%m-%d %H:%M:%S").date()
    start = start + timedelta(
        weeks=0, days=-1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    start = start.strftime("%Y-%m-%d %H:%M:%S")
    end = datetime.strptime(end, "%Y-%m-%d %H:%M:%S").date()
    end = end + timedelta(
        weeks=0, days=1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    end = end.strftime("%Y-%m-%d %H:%M:%S")
    items = DutyInfo.query.filter(DutyInfo.duty_date >= start, DutyInfo.duty_date <= end).all()
    items = [item.data() for item in items]
    return jsonify(items)


@app.route("/duty/query", methods=["GET"])
def queryDutyInfo():
    return jsonify(DutyInfo.query.filter(DutyInfo.id == request.args.get("id")).first().data())


@app.route("/duty/add", methods=["POST"])
def addDutyInfo():
    data = request.form
    dutyInfo = DutyInfo(
        title=data.get("title"),
        duty_date=data.get("duty_date"),
        leader_charge=data.get("leader_charge"),
        leader_duty=data.get("leader_duty"),
        personnel_day=data.get("personnel_day"),
        personnel_night=data.get("personnel_night"),
        duty_phone=data.get("duty_phone"),
        dept=data.get("dept"),
        remark=data.get("remark"),
    )
    try:
        db.session.add(dutyInfo)
        db.session.commit()
        return jsonify({"code": "200", "msg": "insert duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "insert duty info failed!"})


@app.route("/duty/delete", methods=["POST"])
def deleteDutyInfo():
    ids = request.form.get("ids").split(",")
    ids = [int(item) for item in ids]
    try:
        db.session.query(DutyInfo).filter(DutyInfo.id.in_(ids)).delete()
        db.session.commit()
        return jsonify({"code": "200", "msg": "delete duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "delete duty info failed!"})


@app.route("/duty/update", methods=["POST"])
def updateDutyInfo():
    form = request.form
    try:
        db.session.query(DutyInfo).filter(DutyInfo.id == form["id"]).update(form)
        db.session.commit()
        return jsonify({"code": "200", "msg": "update duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "update duty info failed!"})


dutyMapper = {
    "id": "档案Id(可不填)",
    "title": "值班标题(必填项)",
    "duty_date": "值班日期(必填项)",
    "leader_charge": "带班领导(必填项)",
    "leader_duty": "值班领导(必填项)",
    "personnel_day": "白班人员(必填项)",
    "personnel_night": "夜班人员(必填项)",
    "duty_phone": "值班电话(必填项)",
    "dept": "值班部门(必填项)",
    "remark": "备注(可不填)",
}


def get_key(dict_data, value):
    return list(dict_data.keys())[list(dict_data.values()).index(value)]


font = xlwt.Font()
font.height = 20 * 12
font.bold = True
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 70
alignment = xlwt.Alignment()
alignment.horz = 0x02
alignment.vert = 0x01
alignment.wrap = 1
style_1 = xlwt.XFStyle()
style_1.font = font
style_1.pattern = pattern
style_1.alignment = alignment
style_2 = xlwt.XFStyle()
style_2.font = font
style_2.alignment = alignment


@app.route("/duty/template", methods=["GET"])
def templateDutyInfo():
    dutyInfo = DutyInfo.query.filter(DutyInfo.id == 1).first().data(timeFormat="%Y-%m-%d")
    workbook = xlwt.Workbook(encoding="utf-8")
    sheet = workbook.add_sheet("sheet_1")
    for i, (key, value) in enumerate(dutyInfo.items()):
        sheet.col(i).width = 20 * 256
        sheet.write(0, i, dutyMapper[key], style_1)
        sheet.write(1, i, value, style_2)
    buffer = BytesIO()
    workbook.save(buffer)
    workbook.save("template.xls")
    res = make_response(buffer.getvalue())
    res.headers["Content-Disposition"] = "attachment; filename=dutyInfoTemplate.xls"
    res.headers["Content-Type"] = "application/x-xls"
    return res


@app.route("/duty/export", methods=["GET"])
def exportDutyInfo():
    start, end = request.args.get("start"), request.args.get("end")
    start = datetime.strptime(start, "%Y-%m-%d %H:%M:%S").date()
    start = start + timedelta(
        weeks=0, days=-1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    start = start.strftime("%Y-%m-%d %H:%M:%S")
    end = datetime.strptime(end, "%Y-%m-%d %H:%M:%S").date()
    end = end + timedelta(
        weeks=0, days=1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    end = end.strftime("%Y-%m-%d %H:%M:%S")
    items = DutyInfo.query.filter(DutyInfo.duty_date >= start, DutyInfo.duty_date <= end).all()
    items = [item.data(timeFormat="%Y-%m-%d") for item in items]
    workbook = xlwt.Workbook(encoding="utf-8")
    sheet = workbook.add_sheet("sheet_1")
    for i, key in enumerate(items[0].keys()):
        sheet.col(i).width = 20 * 256
        sheet.write(0, i, dutyMapper[key], style_1)
    for row, item in enumerate(items):
        for i, value in enumerate(item.values()):
            sheet.write(row + 1, i, value, style_2)
    buffer = BytesIO()
    workbook.save(buffer)
    res = make_response(buffer.getvalue())
    res.headers["Content-Disposition"] = "attachment; filename=dutyInfo.xls"
    res.headers["Content-Type"] = "application/x-xls"
    return res


@app.route("/duty/import", methods=["POST"])
def importDutyInfo():
    file = request.files["file"]
    workbook = xlrd.open_workbook(file_contents=file.read())
    sheet = workbook.sheet_by_index(0)
    row_num = sheet.nrows
    col_num = sheet.ncols
    json_list = []
    for row in range(row_num):
        if row == 0:
            continue
        row_value = sheet.row_values(row)
        keys = [get_key(dutyMapper, value) for value in sheet.row_values(0)]
        obj = dict()
        for col in range(col_num):
            obj.setdefault(keys[col], row_value[col])
        json_list.append(obj)
    s, f = [], []
    for line, data in enumerate(json_list):
        try:
            dutyInfo = DutyInfo(
                title=data.get("title"),
                duty_date=data.get("duty_date"),
                leader_charge=data.get("leader_charge"),
                leader_duty=data.get("leader_duty"),
                personnel_day=data.get("personnel_day"),
                personnel_night=data.get("personnel_night"),
                duty_phone=data.get("duty_phone"),
                dept=data.get("dept"),
                remark=data.get("remark"),
            )
            db.session.add(dutyInfo)
            s.append(line + 1)
        except:
            s.append(line + 1)
    db.session.commit()
    failInfo = "" if len(f) == 0 else f"失败的数据条目为第 {f} 行，请检查数据格式. "
    return jsonify(
        {"code": "200", "msg": f"成功导入{len(s)}条数据，失败{len(f)}条. ", "failInfo": failInfo}
    )


@app.route("/device/list", methods=["GET"])
def listDevice():
    deviceType = request.args.get("deviceType")
    if deviceType is None or deviceType == "":
        deviceType = ["water", "rain", "cam"]
    else:
        deviceType = [deviceType]
    items = Device.query.filter(Device.type.in_(deviceType)).all()
    items = [item.data() for item in items]
    return jsonify(items)


@app.route("/device/status/set", methods=["POST"])
def setDeviceStatus():
    try:
        Device.query.filter(Device.id == request.form.get("id")).update(request.form)
        db.session.commit()
        return jsonify({"code": "200", "msg": "update device status success!"})
    except:
        return jsonify({"code": "500", "msg": "update device status failed!"})


@app.route("/device/query", methods=["GET"])
def queryDeviceInfo():
    return jsonify(Device.query.filter(Device.id == request.args.get("id")).first().data())


@app.route("/device/add", methods=["POST"])
def addDevice():
    data = request.form
    device = Device(
        eq_id=data.get("eq_id"),
        eq_name=data.get("eq_name"),
        station_id=data.get("station_id"),
        station_name=data.get("station_name"),
        device_type=data.get("type"),
        status=data.get("status"),
        lat=data.get("lat"),
        lng=data.get("lng"),
        addr=data.get("addr"),
        info=data.get("info"),
    )
    try:
        db.session.add(device)
        db.session.commit()
        return jsonify({"code": "200", "msg": "insert duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "insert duty info failed!"})


@app.route("/device/delete", methods=["POST"])
def deleteDevice():
    ids = request.form.get("ids").split(",")
    ids = [int(item) for item in ids]
    try:
        db.session.query(Device).filter(Device.id.in_(ids)).delete()
        db.session.commit()
        return jsonify({"code": "200", "msg": "delete duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "delete duty info failed!"})


@app.route("/device/update", methods=["POST"])
def updateDevice():
    form = request.form
    try:
        db.session.query(Device).filter(Device.id == form["id"]).update(form)
        db.session.commit()
        return jsonify({"code": "200", "msg": "update duty info success!"})
    except:
        return jsonify({"code": "500", "msg": "update duty info failed!"})


deviceMapper = {
    "id": "序号(可不填)",
    "eq_id": "设备id(必填项)",
    "eq_name": "设备名称(必填项)",
    "station_id": "站点id(必填项)",
    "station_name": "站点名称(必填项)",
    "type": "设备类型(必填项)",
    "status": "设备状态(必填项)",
    "lat": "纬度(必填项)",
    "lng": "经度(必填项)",
    "addr": "地址(可不填)",
    "info": "附加信息(可不填)",
}


@app.route("/device/template", methods=["GET"])
def templateDevice():
    device = Device.query.filter(Device.id == 1).first().data()
    workbook = xlwt.Workbook(encoding="utf-8")
    sheet = workbook.add_sheet("sheet_1")
    for i, (key, value) in enumerate(device.items()):
        sheet.col(i).width = 20 * 256
        sheet.write(0, i, deviceMapper[key], style_1)
        sheet.write(1, i, value, style_2)
    buffer = BytesIO()
    workbook.save(buffer)
    res = make_response(buffer.getvalue())
    res.headers["Content-Disposition"] = "attachment; filename=deviceListTemplate.xls"
    res.headers["Content-Type"] = "application/x-xls"
    return res


@app.route("/device/export", methods=["GET"])
def exportDeviceList():
    items = Device.query.all()
    items = [item.data() for item in items]
    workbook = xlwt.Workbook(encoding="utf-8")
    sheet = workbook.add_sheet("sheet_1")
    for i, key in enumerate(items[0].keys()):
        sheet.col(i).width = 20 * 256
        sheet.write(0, i, deviceMapper[key], style_1)
    for row, item in enumerate(items):
        for i, value in enumerate(item.values()):
            sheet.write(row + 1, i, value, style_2)
    buffer = BytesIO()
    workbook.save(buffer)
    res = make_response(buffer.getvalue())
    res.headers["Content-Disposition"] = "attachment; filename=dutyInfo.xls"
    res.headers["Content-Type"] = "application/x-xls"
    return res


@app.route("/device/import", methods=["POST"])
def importDeviceList():
    file = request.files["file"]
    workbook = xlrd.open_workbook(file_contents=file.read())
    sheet = workbook.sheet_by_index(0)
    row_num = sheet.nrows
    col_num = sheet.ncols
    json_list = []
    for row in range(row_num):
        if row == 0:
            continue
        row_value = sheet.row_values(row)
        keys = [get_key(deviceMapper, value) for value in sheet.row_values(0)]
        obj = dict()
        for col in range(col_num):
            obj.setdefault(keys[col], row_value[col])
        json_list.append(obj)
    s, f = [], []
    for line, data in enumerate(json_list):
        try:
            device = Device(
                eq_id=data.get("eq_id"),
                eq_name=data.get("eq_name"),
                station_id=data.get("station_id"),
                station_name=data.get("station_name"),
                device_type=data.get("type"),
                status=data.get("status"),
                lat=data.get("lat"),
                lng=data.get("lng"),
                addr=data.get("addr"),
                info=data.get("info"),
            )
            db.session.add(device)
            s.append(line + 1)
        except:
            s.append(line + 1)
    db.session.commit()
    failInfo = "" if len(f) == 0 else f"失败的数据条目为第 {f} 行，请检查数据格式. "
    return jsonify(
        {"code": "200", "msg": f"成功导入{len(s)}条数据，失败{len(f)}条. ", "failInfo": failInfo}
    )


@app.route("/station", methods=["GET"])
def getStation():
    items = Station.query.filter().all()
    items = [item.data() for item in items]
    for item in items:
        data = {"name": item["station_name"], "station_id": item["station_id"]}
        Location.query.filter(Location.name == item["station_name"]).update(data)
    db.session.commit()
    return jsonify(items)


@app.route("/equipment/list", methods=["GET"])
def getEquipment():
    items = Equipment.query.filter().all()
    items = [item.data() for item in items]
    return jsonify(items)


@app.route("/waterlevel", methods=["GET"])
def getWaterLevel():
    eq_id, start, end = (
        request.args.get("eq_id"),
        request.args.get("start"),
        request.args.get("end"),
    )
    end = datetime.strptime(end, "%Y-%m-%d").date()
    end = end + timedelta(
        weeks=0, days=1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    end = end.strftime("%Y-%m-%d")
    items = WaterLevel.query.filter(
        WaterLevel.eq_id == eq_id, WaterLevel.collect_time >= start, WaterLevel.collect_time <= end
    ).all()
    items = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in items]
    return jsonify(items)


@app.route("/rainfall", methods=["GET"])
def getRainFall():
    eq_id, start, end = (
        request.args.get("eq_id"),
        request.args.get("start"),
        request.args.get("end"),
    )
    end = datetime.strptime(end, "%Y-%m-%d").date()
    end = end + timedelta(
        weeks=0, days=1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    end = end.strftime("%Y-%m-%d")
    items = RainFall.query.filter(
        RainFall.eq_id == eq_id, RainFall.collect_time >= start, RainFall.collect_time <= end
    ).all()
    items = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in items]
    return jsonify(items)


@app.route("/rainfall/dayliy", methods=["GET"])
def getDailyRainFall():
    eq_ids, date = request.args.get("eq_ids"), request.args.get("date")
    print(eq_ids)
    start = date + " 08:00"
    date_gmt = datetime.strptime(date, "%Y-%m-%d").date()
    end = date_gmt + timedelta(
        weeks=0, days=1, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0
    )
    end = end.strftime("%Y-%m-%d") + " 08:00"
    res = []
    for eq_id in eq_ids:
        print(eq_id)
        dayRain = dict()
        station_name = Device.query.filter(Device.eq_id == eq_id).first().data()["station_name"]
        dayRain["station_name"] = station_name
        items = RainFall.query.filter(
            RainFall.eq_id == eq_id, RainFall.collect_time >= start, RainFall.collect_time < end
        ).all()
        items = [item.data(timeFormat="%Y-%m-%d %H:%M") for item in items]
        value = sum([item["value"] for item in items])
        dayRain["value"] = value
        dayRain["update_time"] = items[-1]["collect_time"]
        res.append(dayRain)

    return jsonify(res)


# Function to be executed daily at 0:00
@scheduler.task("interval", days=1, start_date="2023-07-20 00:00:00")
def updateVideoToken():
    url = "https://open.ys7.com/api/lapp/token/get"
    data = {
        "appKey": "23b60fb8e3f64d8ea0b9414a376b4f68",
        "appSecret": "c22dcea153901f9af1c8fcdd146bc47a",
    }
    accessToken = requests.post(url, data).json()["data"]["accessToken"]
    data = {"name": "video", "token": accessToken}
    Token.query.filter(Token.name == "video").update(data)
    db.session.commit()


profiler = Profiler()
profiler.init_app(app)

if __name__ == "__main__":
    updateVideoToken()
    scheduler.api_enabled = True
    scheduler.init_app(app)
    scheduler.start()
    app.run(host="0.0.0.0", port=88)
